﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using Dapper;
using S7PlcDataCollector.Database.Tools;

namespace S7PlcDataCollector.Database.SQLite
{
    // ReSharper disable once IdentifierTypo
    public static class SqliteDbHelper
    {
        public static string ConnectionString { private get; set; } = Config.ConnectionString;

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="model">对象</param>
        /// <returns>数据行RID：0失败，大于0成功</returns>
        public static int Insert(string sql, object model = null)
        {
            var rid = 0;
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                try
                {
                    sql += ";SELECT LAST_INSERT_ROWID();";
                    var ret = conn.ExecuteScalar(sql, model).ToString();
                    rid = int.Parse(ret);
                }
                catch (Exception e)
                {
                    LogHelper.AddErrorLog($"Insert(sql, model) exception: {e.Message}");
                }
            }

            return rid;
        }

        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="modelList">对象集合</param>
        /// <returns>true成功，false失败</returns>
        public static bool Insert(string sql, List<object> modelList)
        {
            return ExecuteNonQuery(sql, modelList);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="model">对象</param>
        /// <returns>true成功，false失败</returns>
        public static bool Update(string sql, object model = null)
        {
            return ExecuteNonQuery(sql, model);
        }

        /// <summary>
        /// 批量更新记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="modelList">对象集合</param>
        /// <returns>true成功，false失败</returns>
        public static bool Update(string sql, List<object> modelList)
        {
            return ExecuteNonQuery(sql, modelList);
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="model">对象</param>
        /// <returns>true成功，false失败</returns>
        public static bool Delete(string sql, object model)
        {
            return ExecuteNonQuery(sql, model);
        }

        /// <summary>
        /// 批量删除记录
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="modelList">对象集合</param>
        /// <returns>true成功，false失败</returns>
        public static bool Delete(string sql, List<object> modelList)
        {
            return ExecuteNonQuery(sql, modelList);
        }

        /// <summary>
        /// 获取数据集合
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="model">对象</param>
        /// <returns>数据集合</returns>
        public static List<T> GetDataList<T>(string sql, object model = null)
        {
            return ExecuteQuery<T>(sql, model);
        }

        #region Execute

        public static bool ExecuteNonQuery(string connectionString, string sql, object model = null)
        {
            var ret = false;
            using (var conn = new SQLiteConnection(connectionString))
            {
                try
                {
                    conn.Execute(sql, model);
                    ret = true;
                }
                catch (Exception e)
                {
                    LogHelper.AddErrorLog($"ExecuteNonQuery(sql, model) exception: {e.Message}.");
                }
            }

            return ret;
        }

        private static bool ExecuteNonQuery(string sql, object model = null)
        {
            return ExecuteNonQuery(ConnectionString, sql, model);
        }

        public static bool ExecuteNonQuery(string sql, List<object> modelList)
        {
            var ret = false;
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                try
                {
                    conn.Execute(sql, modelList);
                    ret = true;
                }
                catch (Exception e)
                {
                    LogHelper.AddErrorLog($"ExecuteNonQuery(sql, modelList) exception: {e.Message}.");
                }
            }

            return ret;
        }

        public static List<T> ExecuteQuery<T>(string sql, object model)
        {
            var retList = new List<T>();
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                try
                {
                    var ret = conn.Query<T>(sql, model);
                    retList.AddRange(ret);
                }
                catch (Exception e)
                {
                    LogHelper.AddErrorLog($"ExecuteQuery(sql, model) exception: {e.Message}.");
                }
            }

            return retList;
        }

        #endregion
    }
}
